package edu.must.uh.util;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.hibernate.mapping.Array;
import edu.must.uh.bean.DeployQueryResultBean;




public class QueryRunnerHelper extends QueryRunner {
    
    private static Logger logger = Logger.getLogger(QueryRunnerHelper.class);
    
    private  QueryRunner qr;
    
    public QueryRunnerHelper(DataSource ds) {        
         qr = new QueryRunner(ds);
    }
    
    private void close() throws SQLException {        
      //  DbUtils.closeQuietly(qr.getDataSource().getConnection());
      //  qr.getDataSource().getConnection().close();        
    }
    
    
    public  List<Map<String,Object>> getlist(String sql)throws SQLException{
        logger.info(sql);       
        List<Map<String,Object>> list = qr.query(sql, new MapListHandler()); 
        logger.info("select :"+list.size());  
        close();
        return list;       
        
    }
    
    public  Map<String,Object> getMap(String sql)throws SQLException{
        logger.info(sql);       
        Map<String,Object> map = qr.query(sql, new MapHandler()); 
        close();
        return map;       
        
    }
    //SQL_CALC_FOUND_ROWS
    private static final String FOUND_ROWS = "SELECT FOUND_ROWS()  AS total_count";    
    public DeployQueryResultBean getLimitlist(String sql){
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        long totalCount = 0L; 
        logger.info(sql);    
        List<Map<String,Object>> resList  = new ArrayList<Map<String,Object>>();
        try {
            conn =  qr.getDataSource().getConnection();
            conn.setAutoCommit(true);
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
     
            ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据
            int columnCount = md.getColumnCount();   //获得列数
            while (rs.next()) {
                Map<String,Object> rowData = new HashMap<String,Object>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), rs.getObject(i));
                }
                resList.add(rowData);
            }     
          
            rs1 = statement.executeQuery(FOUND_ROWS);
            while (rs1.next()){
                totalCount = rs1.getLong("total_count");
            }     
          
        } catch (Exception e) {
            logger.error("getLimitlist() error:", e);
        } finally {            
            DbUtils.closeQuietly(rs);
            DbUtils.closeQuietly(rs1);
            DbUtils.closeQuietly(statement);  
            DbUtils.closeQuietly(conn); 
       }
        return new DeployQueryResultBean(resList,totalCount);
    }
    
    public  <T> int save(String table,T bean) throws Exception { 
        String sql= getSaveSQL(table,bean);     
        logger.info(sql);       
        int row = qr.update(sql);
        System.out.printf("已经有[%d]发生了改变",row);
        close();
        return row;
   }

     public  int save(String sql) throws Exception {  
        logger.info(sql);     
        int row = qr.update (sql);
        System.out.printf("已经有[%d]发生了改变",row);
        close();
        return row;
    }


    public  int disOrEnAble(String table,String enabledMark,String keyFields,String keyValue)throws SQLException{
        String sql=  "UPDATE "+ table+ "SET EnabledMark = "+enabledMark+" WHERE "+ keyFields + "= '"+keyValue+"'";     
        logger.info(sql);  
        int row = qr.update(sql);
        System.out.printf("已经有[%d]发生了改变",row);     
        close();
        return row;
    }    
    
    public int update(String sql) throws SQLException {         
        logger.info(sql);
        int row = qr.update(sql);
        System.out.printf("已经有[%d]发生了改变",row);
        close();      
        return row;
       
    }

    public  int delete(String table,String keyFields,String keyValue)throws SQLException{
        String sql=  "UPDATE "+ table+ " SET DeleteMark = 1 WHERE "+ keyFields + "= '"+keyValue+"'";
        logger.info(sql);  
        int row = qr.update(sql);
        System.out.printf("已经有[%d]发生了改变",row);     
        close();
        return row;
    }

    public  int deleteTrue(String table,String keyFields,String keyValue)throws SQLException{
        String sql=  "DELETE FROM "+ table+ " WHERE "+ keyFields + "= '"+keyValue+"'";        
        logger.info(sql);  
        int row = qr.update( sql);
        System.out.printf("已经有[%d]发生了改变",row);     
        close();
        return row;
    }

    private  String getUpdateSQL(String table,Object obj) throws Exception {
        StringBuilder  sqlString = new StringBuilder();
        BeanInfo beanInfo = Introspector.getBeanInfo(obj.getClass());
        PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
        for (PropertyDescriptor property : propertyDescriptors) {
            String fieldName = property.getName();
            String key = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);            
            Method getter = property.getReadMethod();
            Object valueObject = getter.invoke(obj); 
            if(key.toUpperCase().equals("CLASS")) {
                continue;
            }
            if(valueObject !=null) {               
                if(property.getPropertyType() == java.util.Date.class) {
                    valueObject = ToolsOfDate.toYYYYMMDDHHMISS((Date)valueObject);
                }
                sqlString.append( key+" = '"+ valueObject +"',");               
            }
        }
        return sqlString.deleteCharAt(sqlString.length()-1).toString() ;
    }
    /*
     *  定义方法,使用QueryRunner类的方法update将数据表的数据修改
     */
    public  <T> int update(String table,T bean,String keyFields,String keyValue)throws Exception{
        
        String updateFieldString = getUpdateSQL(table,bean);
        String sql=  "UPDATE "+ table+ " SET "+updateFieldString+" WHERE "+ keyFields + "= '"+keyValue+"'";   
        logger.info(sql); 
        int row = qr.update(sql);
        System.out.printf("已经有[%d]发生了改变",row);
        close();        
        return row;
    }




//INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
private   String getSaveSQL(String table,Object obj) throws Exception {
    StringBuilder  sqlString = new StringBuilder();
    StringBuilder  keyString = new StringBuilder();
    keyString.append(" ( ");
    StringBuilder  valString = new StringBuilder();
    valString.append(" ( ");
    sqlString.append("INSERT INTO " + table);
    BeanInfo beanInfo = Introspector.getBeanInfo(obj.getClass());
    PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
    for (PropertyDescriptor property : propertyDescriptors) {
        String fieldName = property.getName();
        String key = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);            
        Method getter = property.getReadMethod();
        Object valueObject = getter.invoke(obj); 
        if(key.toUpperCase().equals("CLASS")) {
            continue;
        }
        if(valueObject !=null) {
            keyString.append( key+",");
            if(property.getPropertyType() == java.util.Date.class) {
                valueObject = ToolsOfDate.toYYYYMMDDHHMISS((Date)valueObject);
            }
            valString.append("'"+ valueObject +"',");
        }
    }
    return sqlString.toString() + keyString.deleteCharAt(keyString.length()-1).append(")").toString()+"VALUES"+ valString.deleteCharAt(valString.length()-1).append(")").toString();
}

public  <T> List<T>  getlist(String sql, Class<T> oclass) throws Exception {  
   
    logger.info(sql);
    T beanT = oclass.newInstance();       
    List<T> list = qr.query(sql, new BeanListHandler<T>((Class<? extends T>) beanT.getClass()));
    close();
    return list;
}

}
